#region Using

using System;
using System.Collections.Generic;
using System.Collections.Specialized;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.Text;
using System.Globalization;
using BlogEngine.Core;
using BlogEngine.Core.Provider;
using BlogEngine.Core.Provider.Interfaces;

#endregion

namespace BlogEngine.Core.Provider.MSSQL
{
	/// <summary>
	/// Microsoft SQL Server Implementation of BlogProvider
	/// </summary>
	public class CIPhotos : IPhotos
	{

		/// <summary>
		/// Retrieves a photo based on the specified Id.
		/// </summary>
		public Photo Select(Guid id)
		{
			Photo photo = new Photo();
			SQLManagedConnection myConn = SQLManagedConnection.GetConnection(Provider.DatabaseName);

			try
			{
				myConn.GetTextCommand("SELECT PhotoID, Title, Description, Content, DateCreated, DateModified, Author, IsPublished, IsCommentEnabled, Raters, Rating, Slug, Location, [Order], PhotoDate, AlbumID, Width, Height, MD5Hash, FileSize " +
					"FROM be_Photos " +
					"WHERE PhotoID = @id");

				myConn.AddInParameter("@id", DbType.Guid, id);

				using (IDataReader reader = myConn.ExecuteReader())
				{
					reader.Read();

					photo.Id = id;
					photo.Title = reader.GetString(1);
					if (!reader.IsDBNull(2))
						photo.Description = reader.GetString(2);
					if (!reader.IsDBNull(3))
						photo.Content = reader.GetString(3);
					if (!reader.IsDBNull(4))
						photo.DateCreated = reader.GetDateTime(4);
					if (!reader.IsDBNull(5))
						photo.DateModified = reader.GetDateTime(5);
					if (!reader.IsDBNull(6))
						photo.Author = reader.GetString(6);
					if (!reader.IsDBNull(7))
						photo.IsPublished = reader.GetBoolean(7);
					if (!reader.IsDBNull(8))
						photo.IsCommentsEnabled = reader.GetBoolean(8);
					if (!reader.IsDBNull(9))
						photo.Raters = reader.GetInt32(9);
					if (!reader.IsDBNull(10))
						photo.Rating = reader.GetFloat(10);
					if (!reader.IsDBNull(11))
						photo.Slug = reader.GetString(11);
					if (!reader.IsDBNull(12))
						photo.Location = reader.GetString(12);
					if (!reader.IsDBNull(13))
						photo.Order = reader.GetInt32(13);
					if (!reader.IsDBNull(14))
						photo.PhotoDate = reader.GetDateTime(14);
					if (!reader.IsDBNull(15))
						photo.Album = Album.GetById(reader.GetGuid(15));
					if (!reader.IsDBNull(16))
						photo.Width = reader.GetInt32(16);
					if (!reader.IsDBNull(17))
						photo.Height = reader.GetInt32(17);
					if (!reader.IsDBNull(18))
						photo.MD5Hash = reader.GetString(18);
					if (!reader.IsDBNull(19))
						photo.FileSize = reader.GetInt32(19);

					reader.Close();
				}
			}
			finally
			{
				myConn.CloseIfNonTransactional();
			}

			// Tags
			try
			{
				myConn.GetTextCommand("SELECT Tag FROM be_PhotoTag WHERE PhotoID = @id");
				myConn.AddInParameter("@id", DbType.Guid, id);

				using (IDataReader reader = myConn.ExecuteReader())
				{
					while (reader.Read())
						if (!reader.IsDBNull(0))
							photo.Tags.Add(reader.GetString(0));

					reader.Close();
				}
			}
			finally
			{
				myConn.CloseIfNonTransactional();
			}

			// Categories
			try
			{
				myConn.GetTextCommand("SELECT CategoryID FROM be_PhotoCategory WHERE PhotoID = @id");
				myConn.AddInParameter("@id", DbType.Guid, id);

				using (IDataReader reader = myConn.ExecuteReader())
				{
					while (reader.Read())
					{
						Guid key = reader.GetGuid(0);
						if (Category.GetById(key) != null)
							photo.Categories.Add(Category.GetById(key));
					}

					reader.Close();
				}
			}
			finally
			{
				myConn.CloseIfNonTransactional();
			}

			// Comments
			try
			{
				myConn.GetTextCommand("SELECT PhotoCommentID, CommentDate, Author, Email, Website, Comment, Country, Ip, IsApproved " +
					"FROM be_PhotoComment " +
					"WHERE PhotoID = @id");
				myConn.AddInParameter("@id", DbType.Guid, id);

				using (IDataReader reader = myConn.ExecuteReader())
				{
					while (reader.Read())
					{
						Comment comment = new Comment();
						comment.Id = reader.GetGuid(0);
						comment.IsApproved = true;
						comment.Author = reader.GetString(2);
						if (!reader.IsDBNull(4))
						{
							Uri website;
							if (Uri.TryCreate(reader.GetString(4), UriKind.Absolute, out website))
								comment.Website = website;
						}
						comment.Email = reader.GetString(3);
						comment.Content = reader.GetString(5);
						comment.DateCreated = reader.GetDateTime(1);
						comment.Parent = photo;

						if (!reader.IsDBNull(6))
							comment.Country = reader.GetString(6);
						if (!reader.IsDBNull(7))
							comment.IP = reader.GetString(7);
						if (!reader.IsDBNull(8))
							comment.IsApproved = reader.GetBoolean(8);
						else
							comment.IsApproved = true;

						photo.Comments.Add(comment);
					}

					reader.Close();
				}
			}
			finally
			{
				myConn.CloseIfNonTransactional();
			}

			photo.Comments.Sort();

			// Email Notification
			try
			{
				myConn.GetTextCommand("SELECT NotifyAddress FROM be_PhotoNotify WHERE PhotoID = @id");
				myConn.AddInParameter("@id", DbType.Guid, id);

				using (IDataReader reader = myConn.ExecuteReader())
				{
					while (reader.Read())
						if (!reader.IsDBNull(0))
							photo.NotificationEmails.Add(reader.GetString(0));

					reader.Close();
				}
			}
			finally
			{
				myConn.CloseIfNonTransactional();
			}

			// PhotoEffects
			try
			{
				myConn.GetTextCommand("SELECT PhotoEffectID FROM be_PhotoPhotoEffect WHERE PhotoID = @id ORDER BY [Order]");
				myConn.AddInParameter("@id", DbType.Guid, id);

				using (IDataReader reader = myConn.ExecuteReader())
				{
					while (reader.Read())
					{
						Guid key = reader.GetGuid(0);
						if (PhotoEffect.GetById(key) != null)
							photo.PhotoEffects.Add(PhotoEffect.GetById(key));
					}

					reader.Close();
				}
			}
			finally
			{
				myConn.CloseIfNonTransactional();
			}

			return photo;
		}

		/// <summary>
		/// Inserts a new Photo to the data store.
		/// </summary>
		public void Insert(Photo photo)
		{
			if (photo == null)
				throw new ArgumentNullException("photo");

			SQLManagedConnection myConn = SQLManagedConnection.GetConnectionForTransaction(Provider.DatabaseName);

			try
			{
				myConn.GetTextCommand("INSERT INTO be_Photos " +
					  "(PhotoID, Title, Description, Content, DateCreated, DateModified, Author, IsPublished, IsCommentEnabled, Raters, Rating, Slug, Location, [Order], PhotoDate, AlbumId, Width, Height, MD5Hash, FileSize)" +
					  "VALUES (@id, @title, @desc, @content, @created, @modified, @author, @published, @commentEnabled, @raters, @rating, @slug, @location, @order, @photodate, @albumid, @width, @height, @md5hash, @filesize)");

				myConn.AddInParameter("@id", DbType.Guid, photo.Id);
				myConn.AddInParameter("@title", DbType.String, photo.Title);
				myConn.AddInParameter("@desc", DbType.String, photo.Description);
				myConn.AddInParameter("@content", DbType.String, photo.Content);
				myConn.AddInParameter("@created", DbType.DateTime, photo.DateCreated.AddHours(-BlogSettings.Instance.Timezone));
				myConn.AddInParameter("@modified", DbType.DateTime, photo.DateModified.AddHours(-BlogSettings.Instance.Timezone));
				myConn.AddInParameter("@author", DbType.String, photo.Author);
				myConn.AddInParameter("@published", DbType.Boolean, photo.IsPublished);
				myConn.AddInParameter("@commentEnabled", DbType.Boolean, photo.IsCommentsEnabled);
				myConn.AddInParameter("@raters", DbType.Int32, photo.Raters);
				myConn.AddInParameter("@rating", DbType.Single, photo.Rating);
				myConn.AddInParameter("@slug", DbType.String, photo.Slug);
				myConn.AddInParameter("@location", DbType.String, photo.Location);
				myConn.AddInParameter("@order", DbType.Int32, photo.Order);
				myConn.AddInParameter("@photodate", DbType.DateTime, photo.PhotoDate);
				myConn.AddInParameter("@albumid", DbType.Guid, photo.Album.Id);
				myConn.AddInParameter("@width", DbType.Int32, photo.Width);
				myConn.AddInParameter("@height", DbType.Int32, photo.Height);
				myConn.AddInParameter("@md5hash", DbType.String, photo.MD5Hash);
				myConn.AddInParameter("@filesize", DbType.String, photo.FileSize);

				myConn.ExecuteNonQuery();

				//Update associated tables
				UpdateTags(photo, myConn);
				UpdateCategories(photo, myConn);
				UpdateComments(photo, myConn);
				UpdateNotify(photo, myConn);
				UpdatePhotoEffects(photo, myConn);

				myConn.Commit();

				if (!Photo.Items.Contains(photo))
					Photo.Items.Add(photo);

				Photo.Items.Sort();
			}
			finally
			{
				//if commit worked then this will do nothing
				myConn.Rollback();
			}
		}

		/// <summary>
		/// Updates a Photo.
		/// </summary>
		public void Update(Photo photo)
		{
			if (photo == null)
				throw new ArgumentNullException("photo");

			SQLManagedConnection myConn = SQLManagedConnection.GetConnectionForTransaction(Provider.DatabaseName);

			try
			{
				myConn.GetTextCommand("UPDATE be_Photos " +
				  "SET Title = @title, Description = @desc, Content = @content, " +
				  "DateCreated = @created, DateModified = @modified, Author = @Author, " +
				  "IsPublished = @published, IsCommentEnabled = @commentEnabled, " +
				  "Raters = @raters, Rating = @rating, Slug = @slug, Location = @location, [Order] = @order, " +
				  "PhotoDate = @photodate, AlbumId = @albumid " +
				  "WHERE PhotoID = @id");

				myConn.AddInParameter("@id", DbType.Guid, photo.Id);
				myConn.AddInParameter("@title", DbType.String, photo.Title);
				myConn.AddInParameter("@desc", DbType.String, photo.Description);
				myConn.AddInParameter("@content", DbType.String, photo.Content);
				myConn.AddInParameter("@created", DbType.DateTime, photo.DateCreated.AddHours(-BlogSettings.Instance.Timezone));
				myConn.AddInParameter("@modified", DbType.DateTime, photo.DateModified.AddHours(-BlogSettings.Instance.Timezone));
				myConn.AddInParameter("@author", DbType.String, photo.Author);
				myConn.AddInParameter("@published", DbType.Boolean, photo.IsPublished);
				myConn.AddInParameter("@commentEnabled", DbType.Boolean, photo.IsCommentsEnabled);
				myConn.AddInParameter("@raters", DbType.Int32, photo.Raters);
				myConn.AddInParameter("@rating", DbType.Single, photo.Rating);
				myConn.AddInParameter("@slug", DbType.String, photo.Slug);
				myConn.AddInParameter("@location", DbType.String, photo.Location);
				myConn.AddInParameter("@order", DbType.Int32, photo.Order);
				myConn.AddInParameter("@photodate", DbType.DateTime, photo.PhotoDate);
				myConn.AddInParameter("@albumid", DbType.String, photo.Album.Id.ToString());

				myConn.ExecuteNonQuery();

				//Update associated tables
				UpdateTags(photo, myConn);
				UpdateCategories(photo, myConn);
				UpdateComments(photo, myConn);
				UpdateNotify(photo, myConn);

				if (!Photo.Items.Contains(photo))
					Photo.Items.Add(photo);

				Photo.Items.Sort();

				myConn.Commit();
			}
			finally
			{
				//if commit worked then this will do nothing
				myConn.Rollback();
			}
		}

		/// <summary>
		/// Deletes a photo from the data store.
		/// </summary>
		public void Delete(Photo photo)
		{
			if (photo == null)
				throw new ArgumentNullException("photo");

			SQLManagedConnection myConn = SQLManagedConnection.GetConnectionForTransaction(Provider.DatabaseName);

			try
			{
				myConn.GetTextCommand("DELETE FROM be_Photos WHERE PhotoID = @id;" +
									  "DELETE FROM be_PhotoTag WHERE PhotoID = @id;" +
									  "DELETE FROM be_PhotoCategory WHERE PhotoID = @id;" +
									  "DELETE FROM be_PhotoNotify WHERE PhotoID = @id;" +
									  "DELETE FROM be_PhotoPhotoEffect WHERE PhotoID = @id;" +
									  "DELETE FROM be_PhotoComment WHERE PhotoID = @id;");
				myConn.AddInParameter("@id", DbType.Guid, photo.Id);
				myConn.ExecuteNonQuery();
				myConn.Commit();

				if (Photo.Items.Contains(photo))
					Photo.Items.Remove(photo);

				Photo.Items.Sort();
			}
			finally
			{
				//try to rollback and close connection - if commit occured this won't do anything      
				myConn.Rollback();
			}
		}

		/// <summary>
		/// Retrieves all photos from the data store
		/// </summary>
		/// <returns>List of Photos</returns>
		public List<Photo> Fill()
		{
			List<Photo> photos = new List<Photo>();

			//for each entry found, cause a data load to occur - done this way so the reader is shut first!
			foreach (Guid guid in SQLUtil.GetGuids("SELECT PhotoID FROM be_Photos", Provider.DatabaseName))
				photos.Add(Photo.Load(guid));

			photos.Sort();
			return photos;
		}

		private void UpdateTags(Photo photo, SQLManagedConnection conn)
		{
			conn.GetTextCommand("DELETE FROM be_PhotoTag WHERE PhotoID = @id");
			conn.AddInParameter("@id", DbType.Guid, photo.Id);
			conn.ExecuteNonQuery();

			foreach (string tag in photo.Tags)
			{
				conn.GetTextCommand("INSERT INTO be_PhotoTag (PhotoID, Tag) VALUES (@id, @tag)");
				conn.AddInParameter("@id", DbType.Guid, photo.Id);
				conn.AddInParameter("@tag", DbType.String, tag);
				conn.ExecuteNonQuery();
			}
		}

		private void UpdateCategories(Photo photo, SQLManagedConnection conn)
		{
			conn.GetTextCommand("DELETE FROM be_PhotoCategory WHERE PhotoID = @id");
			conn.AddInParameter("@id", DbType.Guid, photo.Id);
			conn.ExecuteNonQuery();

			foreach (Category cat in photo.Categories)
			{
				conn.GetTextCommand("INSERT INTO be_PhotoCategory (PhotoID, CategoryID) VALUES (@id, @cat)");
				conn.AddInParameter("@id", DbType.Guid, photo.Id);
				conn.AddInParameter("@cat", DbType.Guid, cat.Id);
				conn.ExecuteNonQuery();
			}
		}

		private void UpdateComments(Photo photo, SQLManagedConnection conn)
		{
			conn.GetTextCommand("DELETE FROM be_PhotoComment WHERE PhotoID = @id");
			conn.AddInParameter("@id", DbType.Guid, photo.Id);
			conn.ExecuteNonQuery();

			foreach (Comment comment in photo.Comments)
			{
				conn.GetTextCommand("INSERT INTO be_PhotoComment (PhotoCommentID, PhotoID, CommentDate, Author, Email, Website, Comment, Country, Ip, IsApproved) " +
								"VALUES (@photocommentid, @id, @date, @author, @email, @website, @comment, @country, @ip, @isapproved)");
				conn.AddInParameter("@id", DbType.Guid, photo.Id);
				conn.AddInParameter("@photocommentid", DbType.Guid, comment.Id);
				conn.AddInParameter("@date", DbType.DateTime, comment.DateCreated);
				conn.AddInParameter("@author", DbType.String, comment.Author);
				conn.AddInParameter("@email", DbType.String, comment.Email);
				conn.AddInParameter("@website", DbType.String, comment.Website.ToString());
				conn.AddInParameter("@comment", DbType.String, comment.Content);
				conn.AddInParameter("@country", DbType.String, comment.Country);
				conn.AddInParameter("@ip", DbType.String, comment.IP);
				conn.AddInParameter("@isapproved", DbType.Boolean, comment.IsApproved);
				conn.ExecuteNonQuery();
			}
		}

		private void UpdateNotify(Photo photo, SQLManagedConnection conn)
		{
			conn.GetTextCommand("DELETE FROM be_PhotoNotify WHERE PhotoID = @id");
			conn.AddInParameter("@id", DbType.Guid, photo.Id);
			conn.ExecuteNonQuery();

			foreach (string email in photo.NotificationEmails)
			{
				conn.GetTextCommand("INSERT INTO be_PhotoNotify (PhotoID, NotifyAddress) VALUES (@id, @notify)");
				conn.AddInParameter("@id", DbType.Guid, photo.Id);
				conn.AddInParameter("@notify", DbType.String, email);
				conn.ExecuteNonQuery();
			}
		}

		private void UpdatePhotoEffects(Photo photo, SQLManagedConnection conn)
		{
			conn.GetTextCommand("DELETE FROM be_PhotoPhotoEffect WHERE PhotoID = @id");
			conn.AddInParameter("@id", DbType.Guid, photo.Id);
			conn.ExecuteNonQuery();

			int order = 0;
			foreach (PhotoEffect photoeffect in photo.PhotoEffects)
			{
				conn.GetTextCommand("INSERT INTO be_PhotoCategory (PhotoID, PhotoEffectID, [Order]) VALUES (@id, @photoeffectid, @order)");
				conn.AddInParameter("@id", DbType.Guid, photo.Id);
				conn.AddInParameter("@photoeffectid", DbType.Guid, photoeffect.Id);
				conn.AddInParameter("@order", DbType.Int32, order++);
				conn.ExecuteNonQuery();
			}
		}
	}
}
